/*
 * Event_DBImpl.java
 *
 * Created on January 4, 2007, 8:02 PM
 */

package com.asaga.tools;

import java.beans.*;
import java.io.Serializable;
import java.text.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.sql.*;

/**
 * @author mmixon
 */
public class Event_DBImpl extends Object implements Event {
    private int id;
    private String title;
    private String startDate;
    private String endDate;
    private String startTime;
    private String endTime;
    private String description;
    private int classId;
    private int locationId;
    private int groupId;
    private int creatorId;
    private String warningMessage;
    private DBPool connectionPool;
    private Connection connection;
    
    public Event_DBImpl() {
        //constructor
        connectionPool = DBPool.getInstance();
    }
    

    public void addEvent() {
        connection = connectionPool.getConnection();
        String insertEventSQL = "insert into event (title, startDate, endDate, " +
                    "startTime, endTime, description) values (?,?,?,?,?,?)";
        try{
            PreparedStatement ps = connection.prepareStatement(insertEventSQL);
            ps.setString(1, getTitle());
            ps.setDate(2, (java.sql.Date)convertDate(getStartDate()));
            ps.setDate(3, (java.sql.Date)convertDate(getEndDate()));
            ps.setTime(4, convertTime(getStartTime()));
            ps.setTime(5, convertTime(getEndTime()));
            ps.setString(6, getDescription());
            ps.executeUpdate();
            warningMessage = "";
        }
        catch(WrongFormatException wfe){
            //wrong date format
            warningMessage = "wrong date format";
            System.out.println(warningMessage + wfe.getMessage());
        }
        
        catch(SQLException se){
            //
            warningMessage = "wrong sql format";
            System.out.println(warningMessage + se.getMessage());
        }
        
        connectionPool.freeConnection(connection);
    }//end addEvent

    public void removeEvent(int id) {
    }

    public void searchEvent(String searchString) {
    }
    
    public Time convertTime(String time) throws WrongFormatException {
        Date date = new Date();
        DateFormat formatter = new SimpleDateFormat("hh:mm:ss a");
        try {
            date = (java.util.Date)formatter.parse(time);
        } catch (ParseException e) {
            setWarningMessage("Wrong format for date.");
            throw new WrongFormatException(getWarningMessage());
        }
        
        SimpleDateFormat xdf = new SimpleDateFormat("HH:mm:ss");
        java.util.Date dDate = java.sql.Time.valueOf(xdf.format(date));
        return (java.sql.Time)dDate;
        
    }

    /**
     * Convert Date Function
     * @param dateTime date String tobe converted
     * @throws com.asaga.tools.WrongFormatException Throws exception if the string is of the wrong format
     * @return returns the converted date
     */
    public Date convertDate(String dateTime) throws WrongFormatException {
        Date date = new Date();
        DateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
        try {
            date = (java.util.Date)formatter.parse(dateTime);
        } catch (ParseException e) {
            setWarningMessage("Wrong format for date.");
            throw new WrongFormatException(getWarningMessage());
        }
        
        SimpleDateFormat xdf = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date dDate = java.sql.Date.valueOf(xdf.format(date));
        return dDate;
    }
    
     /**
     * Convert Gregorian Date Function
     * @param dateTime date String tobe converted
     * @return returns the converted date
     * @throws com.asaga.tools.WrongFormatException Throws exception if the string is of the wrong format
     */
    public Date convertGregorianDate(Date date) throws WrongFormatException{
        Date newDate = new Date();
        DateFormat formatter = new SimpleDateFormat("EEE MMM dd hh:mm:ss z yyyy");
        try{
            newDate = (java.util.Date)formatter.parse(date.toString());
        } catch(ParseException e){
            setWarningMessage("Wrong format for date.");
            throw new WrongFormatException(getWarningMessage());
        }
        
        SimpleDateFormat xdf = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date dDate = java.sql.Date.valueOf(xdf.format(newDate));
        return dDate;
    }

    public int eventListSize(){
        connection = connectionPool.getConnection();
        String sizeSQL = "select count(*) as numOfEvents from event e";
        int size = 0;
        try{
            java.sql.Statement stmt = connection.createStatement();
            ResultSet result = stmt.executeQuery(sizeSQL);
            result.next();
            size = result.getInt("numOfEvents");
            connectionPool.freeConnection(connection);
        }
        catch(SQLException se){
            // insert exception
        }
        return size;
    }

    public void setTempEvent(int iterator) {
    }

    public int numberOfEvents() {
        return eventListSize();
    }

    public void focusOnEvent(int id) {
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getStartDate() {
        return startDate;
    }

    public void setStartDate(String startDate) {
        this.startDate = startDate;
    }

    public String getEndDate() {
        return endDate;
    }

    public void setEndDate(String endDate) {
        this.endDate = endDate;
    }

    public String getStartTime() {
        return startTime;
    }

    public void setStartTime(String startTime) {
        this.startTime = startTime;
    }

    public String getEndTime() {
        return endTime;
    }

    public void setEndTime(String endTime) {
        this.endTime = endTime;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public int getClassId() {
        return classId;
    }

    public void setClassId(int classId) {
        this.classId = classId;
    }

    public int getLocationId() {
        return locationId;
    }

    public void setLocationId(int locationId) {
        this.locationId = locationId;
    }

    public int getGroupId() {
        return groupId;
    }

    public void setGroupId(int groupId) {
        this.groupId = groupId;
    }

    public int getCreatorId() {
        return creatorId;
    }

    
    
    public void setCreatorId(int creatorId) {
        this.creatorId = creatorId;
    }

    public String getWarningMessage() {
        return warningMessage;
    }

    public void setWarningMessage(String warningMessage) {
        this.warningMessage = warningMessage;
    }
    
    public ResultSet getEventsByPersonId(int id){
        connection = connectionPool.getConnection();
        ResultSet results = null;
        String eventsSQL = "select title, startDate, endDate, startTime, endTime, description" +
                " from event e, person_has_event pe where" +
                " e.eventID = pe.event_eventID";
        try{
            java.sql.Statement stmt = connection.createStatement();
            results = stmt.executeQuery(eventsSQL);
            connectionPool.freeConnection(connection);
        }
        catch(SQLException se){
            warningMessage = "SQL Error: " + se.getMessage() + se.getSQLState();
        }
        return results; 
    }

    public ResultSet getEventsByState(String state) {
        connection = connectionPool.getConnection();
        ResultSet results = null;
        String eventsSQL = "SELECT e.title AS event_title, startDate, endDate, description " +
                "FROM event e, person p, location l, event_has_location el WHERE " +
                "e.eventID = el.event_eventID AND " +
                "el.location_locationID = l.locationID AND " +
                "p.state = l.state AND " +
                "e.startDate >= (SELECT current_date())";
        try{
            java.sql.Statement stmt = connection.createStatement();
            results = stmt.executeQuery(eventsSQL);
            connectionPool.freeConnection(connection);
        }
        catch(SQLException se){
            warningMessage = "SQL Error: " + se.getMessage() + se.getSQLState();
        }
    
        return results;
    }
    
}
